﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using RCA1._0;
using System.Data.OleDb;

namespace Royal_1._0_DAL_WS
{
    public class TruSoCongTyDAO: DataProvider
    {
        public DataTable LayBang()
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "select * from TRUSOCONGTY";
            OleDbDataAdapter da = new OleDbDataAdapter(strSQL, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        #region "Lấy đối tượng"
        private TruSoCongTyDTO LayDoiTuong(String sql)
        {
            OleDbConnection cn = this.Connect();

            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();

            TruSoCongTyDTO tempDTO = null;
            if (dr.Read())
            {
                tempDTO = new TruSoCongTyDTO();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.IDTruSoCongTy = Convert.ToInt32(dr["IDTruSoCongTy"]);
                tempDTO.MaLoaiTruSo = Convert.ToInt32(dr["MaLoaiTruSo"]);
                tempDTO.DiaChi = dr["DiaChi"].ToString();
                tempDTO.Fax = dr["Fax"].ToString();
                tempDTO.DienThoai = dr["DienThoai"].ToString();
                tempDTO.TinhTrangTruSoCongTy = Convert.ToInt32(dr["TinhTrangTruSoCongTy"]);

            }
            cn.Close();
            return tempDTO;
        }
        #endregion
        

        #region "Lấy danh sách"
        private List<TruSoCongTyDTO> LayDSach(String sql)
        {
            OleDbConnection cn = this.Connect();
            OleDbCommand cmd = new OleDbCommand(sql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            TruSoCongTyDTO tempDTO;
            List<TruSoCongTyDTO> dsTRUSOCONGTY = new List<TruSoCongTyDTO>();
            while (dr.Read())
            {
                tempDTO = new TruSoCongTyDTO();
                tempDTO.MaCongTy = Convert.ToInt32(dr["MaCongTy"]);
                tempDTO.IDTruSoCongTy = Convert.ToInt32(dr["IDTruSoCongTy"]);
                tempDTO.MaLoaiTruSo = Convert.ToInt32(dr["MaLoaiTruSo"]);
                tempDTO.DiaChi = dr["DiaChi"].ToString();
                tempDTO.Fax = dr["Fax"].ToString();
                tempDTO.DienThoai = dr["DienThoai"].ToString();
                tempDTO.TinhTrangTruSoCongTy = Convert.ToInt32(dr["TinhTrangTruSoCongTy"]);
                dsTRUSOCONGTY.Add(tempDTO);
            }
            return dsTRUSOCONGTY;
        }

        public List<TruSoCongTyDTO> LayDanhSachTheoMaCongTy(int MaCongTy)
        {
            String sql = "Select * From TRUSOCONGTY Where TinhTrangTruSoCongTy = 1 and MaCongTy = " + MaCongTy;
            return LayDSach(sql);
        }

        public List<TruSoCongTyDTO> LayDanhSachTheoLoaiTruSoVaMaCongTy(int maLoaiTruSo, int maCongTy)
        {
            String sql = "Select * From TRUSOCONGTY Where TinhTrangTruSoCongTy = 1 and MaCongTy = " + maCongTy+" and MaLoaiTruSo="+maLoaiTruSo;
            return LayDSach(sql);
        }

        #endregion


        #region "Tìm kiếm"
        public TruSoCongTyDTO TimTheoMa(int MaTruSoCongTy)
        {

            string strSQL = "select * from TRUSOCONGTY Where IDTruSoCongTy=" + MaTruSoCongTy.ToString();
            return LayDoiTuong(strSQL);
        }

        public TruSoCongTyDTO TimTruSoChinhTheoMaCongTy(int MaCongTy)
        {
            string strSQL = "select * from TRUSOCONGTY Where TinhTrangTruSoCongTy=1 and MaLoaiTruSo= 1 and MaCongTy=" + MaCongTy;
            return LayDoiTuong(strSQL);
        }

        public TruSoCongTyDTO TimTruSoNhanThongBaoThueTheoMaCongTy(int MaCongTy)
        {
            string strSQL = "select * from TRUSOCONGTY Where TinhTrangTruSoCongTy=1 and MaLoaiTruSo= 4 and MaCongTy=" + MaCongTy;
            return LayDoiTuong(strSQL);
        }
        #endregion


        #region "Thêm"
        public void Them(TruSoCongTyDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "insert into TRUSOCONGTY(DiaChi, DienThoai, Fax, MaLoaiTruSo, MaCongTy, TinhTrangTruSoCongTy) values (@DiaChi, @DienThoai, @Fax, @MaLoaiTruSo, @MaCongTy, @TinhTrangTruSoCongTy)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@DiaChi", OleDbType.LongVarWChar).Value = tempDTO.DiaChi;
            cmd.Parameters.Add("@DienThoai", OleDbType.LongVarWChar).Value = tempDTO.DienThoai;
            cmd.Parameters.Add("@Fax", OleDbType.LongVarWChar).Value = tempDTO.Fax;
            cmd.Parameters.Add("@MaLoaiTruSo", OleDbType.Integer).Value = tempDTO.MaLoaiTruSo;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
            cmd.Parameters.Add("@TinhTrangTruSoCongTy", OleDbType.Integer).Value = tempDTO.TinhTrangTruSoCongTy;

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


        #region "Xóa"

        public void Xoa(int MaTruSoCongTy)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update TRUSOCONGTY set TinhTrangTruSoCongTy = 2 where IDTruSoCongTy = " + MaTruSoCongTy.ToString();
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        #endregion


        #region "Sửa"
        public void Sua(TruSoCongTyDTO tempDTO)
        {
            OleDbConnection cn = this.Connect();
            string strSQL = "update TRUSOCONGTY set DiaChi = @DiaChi, DienThoai = @DienThoai, Fax = @Fax, MaLoaiTruSo = @MaLoaiTruSo, MaCongTy = @MaCongTy, TinhTrangTruSoCongTy = @TinhTrangTruSoCongTy where IDTruSoCongTy = @IDTruSoCongTy";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@DiaChi", OleDbType.LongVarWChar).Value = tempDTO.DiaChi;
            cmd.Parameters.Add("@DienThoai", OleDbType.LongVarWChar).Value = tempDTO.DienThoai;
            cmd.Parameters.Add("@Fax", OleDbType.LongVarWChar).Value = tempDTO.Fax;
            cmd.Parameters.Add("@MaLoaiTruSo", OleDbType.Integer).Value = tempDTO.MaLoaiTruSo;
            cmd.Parameters.Add("@MaCongTy", OleDbType.Integer).Value = tempDTO.MaCongTy;
            cmd.Parameters.Add("@TinhTrangTruSoCongTy", OleDbType.Integer).Value = tempDTO.TinhTrangTruSoCongTy;
            cmd.Parameters.Add("@IDTruSoCongTy", OleDbType.Integer).Value = tempDTO.IDTruSoCongTy;
            cmd.ExecuteNonQuery();
            cn.Close();
        }

        #endregion
    }
}